from collections import Counter, defaultdict
import math
from app.dao.student_statistic_dao import StudentStatisticDAO
from app.dao.models.mysql_gen import StudentScore
from app.dao.student_score_dao import StudentScoreDAO
from typing import List
import pandas as pd
import numpy as np


#
major_course_code = {
    "编辑出版学": [
        "3150260011061",
        "3150260012063",
        "3150260012075",
        "3150260012070",
        "3150260011057",
        "3150260012066",
        "3150260012069",
        "3350260011125",
        "3150260012065",
        "3150260011108",
        "3150260011047",
        "3140260011015",
        "3140260011014",
        "3140260012011",
        "3140260011005",
        "3140260011009",
        "3140260011008",
        "3140260011007",
        "3140260011006",
        "3140260011010",
        "3140260011012",
        "3140260011013",
        "3150260011107",
        "1100260011001",
        "3350260011116",
        "3350260011135",
        "3350260011004",
        "3350260011140",
        "3350260011113",
        "3350260012111",
        "3350260011112",
        "3350260011136",
        "3350260011120",
        "3350260011130",
        "3350260011114",
        "3350260011138",
        "3350260011109",
        "3000260011123",
        "3350260011126",
        "3350260011122",
        "3350260011129",
        "3350260011141",
        "3350260011148",
        "3350260011142",
        "3350260011147",
        "3350260011124",
        "3350260011127",
        "3350260011115",
        "3350260011144",
        "3350260011154",
        "3350260011146",
        "3350260011145",
        "3350260011143",
        "3350260011110",
        "3350260011150",
        "3350260011151",
        "3350260011153",
        "3000260011140",
        "3000260011091",
        "4350260011118",
        "3000260011110",
        "3000260011137",
        "3350850011050",
        "1100840011006",
        "1100890011009",
        "1000890011010",
        "1100890011008",
        "1100890011002",
        "1000890011009",
        "1100730011001",
        "100830011001",
        "2110720011001",
        "2110720011002",
    ],
    "信息管理与信息系统": [
        "3150260011080",
        "3150260011079",
        "3150260011090",
        "3150260011108",
        "3150260013082",
        "3150260013081",
        "3150260011087",
        "3150260011091",
        "3150260011089",
        "3150260011088",
        "3150260011092",
        "3140260011005",
        "3140260011015",
        "3140260011014",
        "3140260012011",
        "3140260011007",
        "3140260011013",
        "3140260011012",
        "3140260011010",
        "3140260011008",
        "3140260011006",
        "3140260011009",
        "3150850011048",
        "3150260011107",
        "3150260011083",
        "3150260011084",
        "3150260011085",
        "1100260011001",
        "3350260011126",
        "3350260011114",
        "3000260011123",
        "3350260011135",
        "3350260011109",
        "3350260011140",
        "3350260011113",
        "3350260011112",
        "3350260011004",
        "3350260011136",
        "3350260011138",
        "3350260012111",
        "3350260011116",
        "3350260011120",
        "3350260011130",
        "3350260011129",
        "3350260011110",
        "3350260011148",
        "3350260011153",
        "3350260011147",
        "3350260011151",
        "3350260011154",
        "3350260011143",
        "3350260011124",
        "3350260011144",
        "3350260011125",
        "3350260011142",
        "3350260011127",
        "3350260011145",
        "3350260011141",
        "3350260011146",
        "3350260011150",
        "3350260011115",
        "3350260011122",
        "3000260011140",
        "4350260011118",
        "3000260011110",
        "3000260011137",
        "3350850011050",
        "1100840011006",
        "1100890011009",
        "1000890011010",
        "1100890011008",
        "1100890011002",
        "1000890011009",
        "1100730011001",
        "100830011001",
        "2110720011001",
        "2110720011002",
    ],
    "电子商务": [
        "3150260011104",
        "3150260011106",
        "3150260011105",
        "3150260011108",
        "3150260011096",
        "3150260011097",
        "3150260011099",
        "3150260011098",
        "3140260011005",
        "3140260011015",
        "3140260011014",
        "3140260012011",
        "3140260011009",
        "3140260011006",
        "3140260011007",
        "3140260011010",
        "3140260011013",
        "3140260011008",
        "3140260011012",
        "3150850011048",
        "3150260011107",
        "3150260011095",
        "1100260011001",
        "3350260011126",
        "3350260011114",
        "3000260011123",
        "3350260011136",
        "3350260011004",
        "3350260011138",
        "3350260011116",
        "3350260011112",
        "3350260011140",
        "3350260011135",
        "3350260011109",
        "3350260011120",
        "3350260011130",
        "3350260012111",
        "3350260011113",
        "3350260011122",
        "3350260011125",
        "3350260011143",
        "3350260011148",
        "3350260011150",
        "3350260011145",
        "3350260011142",
        "3350260011151",
        "3350260011110",
        "3350260011124",
        "3350260011147",
        "3350260011153",
        "3350260011129",
        "3350260011141",
        "3350260011146",
        "3350260011127",
        "3350260011115",
        "3350260011144",
        "3350260011154",
        "3000260011140",
        "4350260011118",
        "3000260011110",
        "3000260011137",
        "3350850011050",
        "1100890011009",
        "1100840011006",
        "1000890011010",
        "1100890011008",
        "1100890011002",
        "1100730011001",
        "1000890011009",
        "100830011001",
        "2110720011001",
        "2110720011002",
    ],
    "图书馆学": [
        "3150260011025",
        "3150260011023",
        "3150260011021",
        "3150260011031",
        "3150260011020",
        "3150260011022",
        "3150260011024",
        "3150260011027",
        "3150260011108",
        "3150260011030",
        "3150260011029",
        "3150260011019",
        "3140260011005",
        "3140260011014",
        "3140260012011",
        "3140260011015",
        "3140260011012",
        "3140260011008",
        "3140260011010",
        "3140260011007",
        "3140260011009",
        "3140260011006",
        "3140260011013",
        "3150260011107",
        "1100260011001",
        "3350260011126",
        "3350260011114",
        "3000260011123",
        "3350260011140",
        "3350260011113",
        "3350260011135",
        "3350260011138",
        "3350260011120",
        "3350260012111",
        "3350260011112",
        "3350260011116",
        "3350260011004",
        "3350260011109",
        "3350260011136",
        "3350260011130",
        "3350260011124",
        "3350260011147",
        "3350260011145",
        "3350260011141",
        "3350260011122",
        "3350260011110",
        "3350260011146",
        "3350260011144",
        "3350260011151",
        "3350260011143",
        "3350260011129",
        "3350260011127",
        "3350260011150",
        "3350260011115",
        "3350260011142",
        "3350260011153",
        "3350260011148",
        "3350260011125",
        "3350260011154",
        "3000260011140",
        "4350260011118",
        "3000260011110",
        "3000260011137",
        "3350850011050",
        "1100890011009",
        "1100840011006",
        "1000890011010",
        "1000890011009",
        "1100890011002",
        "1100730011001",
        "1100890011008",
        "100830011001",
        "2110720011001",
        "2110720011002",
    ],
    "档案学": [
        "3150260013041",
        "3150260011034",
        "3150260011032",
        "3150260011037",
        "3150260011043",
        "3150260011038",
        "3150260011108",
        "3150260011033",
        "3150260011035",
        "3150260011045",
        "3150260011036",
        "3150260011040",
        "3140260012011",
        "3140260011005",
        "3140260011014",
        "3140260011015",
        "3140260011010",
        "3140260011013",
        "3140260011009",
        "3140260011007",
        "3140260011012",
        "3140260011008",
        "3140260011006",
        "3150260011107",
        "1100260011001",
        "3350260011004",
        "3350260011116",
        "3350260011135",
        "3350260011112",
        "3350260011138",
        "3350260011109",
        "3350260011130",
        "3000260011123",
        "3350260011136",
        "3350260011126",
        "3350260012111",
        "3350260011140",
        "3350260011113",
        "3350260011114",
        "3350260011120",
        "3350260011143",
        "3350260011142",
        "3350260011125",
        "3350260011148",
        "3350260011115",
        "3350260011153",
        "3350260011110",
        "3350260011144",
        "3350260011122",
        "3350260011150",
        "3350260011129",
        "3350260011145",
        "3350260011124",
        "3350260011141",
        "3350260011146",
        "3350260011154",
        "3350260011127",
        "3350260011147",
        "3350260011151",
        "3000260011140",
        "4350260011118",
        "3000260011110",
        "3000260011137",
        "3350850011050",
        "1100840011006",
        "1100890011009",
        "1000890011010",
        "1000890011009",
        "1100890011002",
        "1100730011001",
        "1100890011008",
        "100830011001",
        "2110720011001",
        "2110720011002",
    ],
    "大数据管理与应用": [
        "3000260011096",
        "3150260011080",
        "3000260011104",
        "3000260011097",
        "3150260011079",
        "3000260011092",
        "3000260011098",
        "3000260011093",
        "3000260011102",
        "3000260011103",
        "3150260011108",
        "3000260011094",
        "3140260011005",
        "3140260011015",
        "3140260011014",
        "3140260012011",
        "3140260011007",
        "3140260011013",
        "3140260011012",
        "3140260011010",
        "3140260011008",
        "3140260011006",
        "3140260011009",
        "3150850011048",
        "3150260011107",
        "1100260011001",
        "3350260011126",
        "3350260011114",
        "3000260011123",
        "3350260011135",
        "3350260011109",
        "3350260011140",
        "3350260011113",
        "3350260011112",
        "3350260011004",
        "3350260011136",
        "3350260011138",
        "3350260012111",
        "3350260011116",
        "3350260011120",
        "3350260011130",
        "3350260011129",
        "3350260011110",
        "3350260011148",
        "3350260011153",
        "3350260011147",
        "3350260011151",
        "3350260011154",
        "3350260011143",
        "3350260011124",
        "3350260011144",
        "3350260011125",
        "3350260011142",
        "3350260011127",
        "3350260011145",
        "3350260011141",
        "3350260011146",
        "3350260011150",
        "3350260011115",
        "3350260011122",
        "3000260011140",
        "4350260011118",
        "3000260011110",
        "3000260011137",
        "3350850011050",
        "1100840011006",
        "1100890011009",
        "1000890011010",
        "1100890011008",
        "1100890011002",
        "1000890011009",
        "1100730011001",
        "100830011001",
        "2110720011001",
        "2110720011002",
    ],
    "图书馆学（本硕博）": [
        "3200260011017",
        "3150260011023",
        "3000260011125",
        "3000260011127",
        "3000260011128",
        "3200260011016",
        "3150260011108",
        "3000260011132",
        "3150260011029",
        "3150260011079",
        "3150260011080",
        "3000260011130",
        "3150260011020",
        "3000260011126",
        "3150260011019",
        "3140260011015",
        "3140260011014",
        "3140260011005",
        "3140260012011",
        "3140260011008",
        "3140260011007",
        "3140260011006",
        "3140260011012",
        "3140260011013",
        "3140260011010",
        "3140260011009",
        "3150850011048",
        "3150260011107",
        "1100260011001",
        "3000260011123",
        "3350260011126",
        "3350260011114",
        "3350260011135",
        "3350260011109",
        "3350260011140",
        "3350260011113",
        "3350260011112",
        "3350260011004",
        "3350260011136",
        "3350260011138",
        "3350260012111",
        "3350260011116",
        "3350260011120",
        "3350260011130",
        "3350260011110",
        "3350260011148",
        "3350260011153",
        "3350260011147",
        "3350260011151",
        "3350260011154",
        "3350260011129",
        "3350260011143",
        "3350260011124",
        "3350260011144",
        "3350260011125",
        "3350260011142",
        "3350260011127",
        "3350260011145",
        "3350260011141",
        "3350260011146",
        "3350260011150",
        "3350260011115",
        "3350260011122",
        "3000260011140",
        "4350260011118",
        "3000260011110",
        "3000260011137",
        "3350850011050",
        "1100840011006",
        "1100890011009",
        "1000890011010",
        "1100890011008",
        "1100890011002",
        "1000890011009",
        "1100730011001",
        "100830011001",
    ],
    "档案学（本硕博）": [
        "3000260011128",
        "3000260011127",
        "3000260011125",
        "3150260011032",
        "3000260011130",
        "3000260011132",
        "3150260011108",
        "3150260011080",
        "3150260011079",
        "3150260011033",
        "3000260011126",
        "3150260011038",
        "3140260011015",
        "3140260011014",
        "3140260011005",
        "3140260012011",
        "3140260011008",
        "3140260011007",
        "3140260011006",
        "3140260011012",
        "3140260011013",
        "3140260011010",
        "3140260011009",
        "3150850011048",
        "3150260011107",
        "1100260011001",
        "3350260011114",
        "3350260011004",
        "3350260011136",
        "3350260011120",
        "3350260011135",
        "3350260011109",
        "3350260011138",
        "3350260012111",
        "3350260011126",
        "3350260011116",
        "3000260011123",
        "3350260011140",
        "3350260011130",
        "3350260011113",
        "3350260011112",
        "3000260011121",
        "3350260011151",
        "3350260011154",
        "3350260011127",
        "3350260011147",
        "3350260011148",
        "3350260011129",
        "3350260011143",
        "3350260011144",
        "3350260011122",
        "3350260011110",
        "3350260011124",
        "3350260011125",
        "3350260011142",
        "3350260011145",
        "3350260011141",
        "3350260011146",
        "3350260011150",
        "3350260011115",
        "3350260011153",
        "3000260011140",
        "4350260011118",
        "3000260011110",
        "3000260011137",
        "3350850011050",
        "1100840011006",
        "1100890011009",
        "1000890011010",
        "1100890011008",
        "1100890011002",
        "1000890011009",
        "1100730011001",
        "100830011001",
        "2110720011001",
        "2110720011002",
    ],
    "编辑出版学（本硕博）": [
        "3000260011128",
        "3000260011127",
        "3000260011125",
        "3150260011061",
        "3150260012063",
        "3000260011132",
        "3150260011108",
        "3000260011130",
        "3350260011125",
        "3150260011079",
        "3150260011080",
        "3000260011126",
        "3150260011047",
        "3140260011005",
        "3140260011015",
        "3140260012011",
        "3140260011014",
        "3140260011012",
        "3140260011009",
        "3140260011006",
        "3140260011007",
        "3140260011008",
        "3140260011013",
        "3140260011010",
        "3150850011048",
        "3150260011107",
        "1100260011001",
        "3000260011123",
        "3350260011126",
        "3350260011114",
        "3350260011135",
        "3350260011109",
        "3350260011140",
        "3350260011113",
        "3350260011112",
        "3350260011004",
        "3350260011136",
        "3350260011138",
        "3350260012111",
        "3350260011116",
        "3350260011120",
        "3350260011130",
        "3350260011148",
        "3350260011153",
        "3350260011147",
        "3350260011151",
        "3350260011154",
        "3350260011110",
        "3350260011129",
        "3350260011143",
        "3350260011124",
        "3350260011144",
        "3350260011142",
        "3350260011127",
        "3350260011145",
        "3350260011141",
        "3350260011146",
        "3350260011150",
        "3350260011115",
        "3350260011122",
        "3000260011140",
        "4350260011118",
        "3000260011110",
        "3000260011137",
        "3350850011050",
        "1100840011006",
        "1100890011009",
        "1000890011010",
        "1100890011008",
        "1100890011002",
        "1000890011009",
        "1100730011001",
        "100830011001",
        "2110720011001",
        "2110720011002",
    ],
}

major_code_set = {}
for key in major_course_code.keys():
    major_code_set[key] = set(major_course_code[key])


# utils/mapping.py
excel_to_model_field = {
    "班级": "class_name",
    "学号": "student_id",
    "姓名": "name",
    "课程名称": "course_name",
    "成绩": "score",
    "学年": "academic_year",
    "学期": "semester",
    "学生类别": "student_type",
    "学院": "college",
    "专业": "major",
    "年级": "grade",
    "学生标记": "student_tag",
    "开课学院": "teaching_college",
    "课程代码": "course_code",
    "教学班": "teaching_class",
    "任课教师": "teacher",
    "学分": "credit",
    "成绩备注": "score_note",
    "考试性质": "exam_type",
    "绩点": "gpa",
    "课程标记": "course_tag",
    "课程类别": "course_category",
    "课程归属": "course_belonging",
    "课程性质": "course_nature",
    "考核方式": "assessment_method",
    "是否成绩作废": "is_score_invalid",
    "提交人": "submitter",
    "提交时间": "submit_time",
    "是否学位课程": "is_degree_course",
    "性别": "gender",
    "专业方向": "major_direction",
    "课程英文名称": "course_english_name",
    "备注信息": "remarks",
    "学分绩点": "credit_gpa",
    "开课类型": "course_type",
}


def clean_and_map_record(row: dict) -> dict:
    cleaned = {}

    if row["专业"] == "社会科学试验班（信息管理类）":
        return None

    if "2022301041049" == str(row["学号"]) or "2022335550037" == str(row["学号"]):
        return None

    if row["课程标记"] == "辅修":  # 不计算辅修
        return None

    if row["课程性质"] is not None and "必修" not in row["课程性质"]:
        return None

    # 如果不在上面，但是在我们自己学院的表里，也可以
    codes = major_code_set[row["专业"]]

    # 需要根据学部进行筛选
    if (
        str(row["开课学院"]).strip()
        not in [
            "大学生心理健康中心",
            "计算中心",
            "军事教研室",
            "公共数学教学",
            "形势与政策教育中心",
            "体育部",
            "大学英语部",
            "公共政治教学",
            "信息管理学院",
        ]
        and not str(row["课程代码"]) in codes
    ):
        return None

    if row["成绩"] == "W" or row["成绩备注"] == "中期退课":  # 不计算辅修
        return None

    for zh_key, value in row.items():
        field = excel_to_model_field.get(zh_key)
        if not field:
            continue

        # ✅ 处理本硕博特例：直接赋值并跳过后续逻辑
        if field == "major" and isinstance(value, str) and value.endswith("（本硕博）"):
            cleaned[field] = "本硕博贯通班"
            continue

        # if (
        #     field == "major"
        #     and isinstance(value, str)
        #     and value.endswith("社会科学试验班（信息管理类）")
        # ):
        #     cleaned[field] = "信息管理与信息系统"
        #     continue

        # 字段级转换
        if field in {"is_score_invalid", "is_degree_course"}:
            cleaned[field] = str(value).strip() == "是"
        elif field in {"credit", "gpa", "credit_gpa", "score"}:
            try:
                cleaned[field] = float(value)
            except:
                cleaned[field] = None
        elif field == "submit_time":
            try:
                cleaned[field] = pd.to_datetime(value)
            except:
                cleaned[field] = None
        else:
            cleaned[field] = str(value).strip() if isinstance(value, str) else value

    return clean_nan_dict(cleaned)


def replace_nan_with_none(value):
    if value is None:
        return None
    if isinstance(value, float) and math.isnan(value):
        return 0
    if isinstance(value, (np.float64, np.float32)) and np.isnan(value):
        return 0
    if isinstance(value, pd.Timestamp) and pd.isna(value):
        return None
    return value


def clean_nan_dict(record: dict) -> dict:
    return {k: replace_nan_with_none(v) for k, v in record.items()}


class StudentScoreService:

    @staticmethod
    def import_scores(score_dicts: List[dict]):
        cleaned_records = []
        for d in score_dicts:
            cleaned = clean_and_map_record(d)
            if cleaned is None:  # 跳过无效记录
                continue
            cleaned_records.append(StudentScore(**cleaned))
        StudentScoreDAO.add_batch(cleaned_records)

        # ✅ 1. 对每个学生的每门课只保留最低绩点的一次成绩
        course_min_map = {}  # key = (student_id, course_name)
        for record in cleaned_records:
            key = (record.student_id, record.course_name)
            cur_cgpa = record.credit_gpa or 0
            if key not in course_min_map or (
                cur_cgpa < (course_min_map[key].credit_gpa or 0)
            ):
                course_min_map[key] = record

        deduplicated_records = list(course_min_map.values())

        # ✅ 2. 聚合每个学生的数据
        stats_map = defaultdict(
            lambda: {"course_count": 0, "total_credit": 0.0, "total_credit_gpa": 0.0}
        )

        for record in deduplicated_records:
            sid = record.student_id
            stats = stats_map[sid]
            stats["student_id"] = sid
            stats["name"] = record.name
            stats["major"] = record.major
            stats["student_tag"] = record.student_tag
            stats["course_count"] += 1
            stats["total_credit"] += record.credit or 0
            stats["total_credit_gpa"] += record.credit_gpa or 0

        statistics = []
        for stat in stats_map.values():
            credit = stat["total_credit"]
            stat["avg_gpa"] = (
                round(stat["total_credit_gpa"] / credit, 10) if credit else 0.0
            )
            statistics.append(stat)

        # ✅ 分组
        grouped_by_major = defaultdict(list)
        for stat in statistics:
            grouped_by_major[stat["major"]].append(stat)

        # ✅ 排序 + 赋 rank + 赋 major_top_gpa
        for major, students in grouped_by_major.items():
            # 按 GPA 降序
            students.sort(key=lambda x: x["avg_gpa"], reverse=True)

            top_gpa = students[0]["avg_gpa"] if students else 0.0

            last_gpa = None
            last_rank = 0
            for idx, student in enumerate(students, start=1):
                # 并列处理（可选）
                if student["avg_gpa"] == last_gpa:
                    student["gpa_rank"] = last_rank
                else:
                    student["gpa_rank"] = idx
                    last_rank = idx
                    last_gpa = student["avg_gpa"]

                # ✅ 加上专业最高 GPA 字段
                student["major_top_gpa"] = top_gpa

        # ✅ 1. 统计每个专业人数
        major_counts = Counter(stat["major"] for stat in statistics)

        # ✅ 2. 计算 ranked_score
        for stat in statistics:
            gpa = stat["avg_gpa"]
            top_gpa = stat["major_top_gpa"] or 1e-6  # 避免除以0
            rank = stat["gpa_rank"]
            total = major_counts[stat["major"]]

            # 判断是否体育特长生
            is_sport_special = "高水平运动队" in (stat["student_tag"] or "")

            weight = 80 if is_sport_special else 90

            stat["ranked_score"] = round(
                (gpa * weight / top_gpa) * ((total - rank + 1) / total), 10
            )

        # ✅ 3. 清空旧数据并写入统计表
        StudentStatisticDAO.delete_all()
        StudentStatisticDAO.update_statistics(statistics)

    @staticmethod
    def get_scores_by_student(student_id: str):
        return StudentScoreDAO.get_by_student_id(student_id)

    @staticmethod
    def get_statistics_by_major(major: str):
        return StudentStatisticDAO.get_by_major(major)

    @staticmethod
    def get_statistics_by_sid(student_id: str):
        return StudentStatisticDAO.get_by_sid(student_id)

    @staticmethod
    def get_scores_for_student(student_id: str) -> List[StudentScore]:
        return StudentScoreDAO.get_scores_by_student_id(student_id)
